# encoding:utf8

# 加载数据库配置脚本


from Models.models import Role, user, run_mode, basic_sql_command, db_recover_scheme_detail, db_recover_scheme, \
    scheme_attr
from flask_sqlalchemy import SQLAlchemy

#
db = SQLAlchemy()


# def db_init_user_role():  # 这里也可以不加 形参数, 上面已加载 user,Role,db
#     admin_role = Role(name='Admin')
#     mod_role = Role(name='Moderator')
#     user_role = Role(name='User')
#     user_john = user(
#         username='john',
#         password='123',
#         email='aa@aa.com',
#         role=admin_role)
#     user_aa = user(
#         username='aa',
#         password='123',
#         email='aa1@aa.com',
#         role=admin_role)
#     user_susan = user(
#         username='susan',
#         password='123',
#         email='aa2@aa.com',
#         role=user_role)
#     user_david = user(
#         username='david',
#         password='123',
#         email='aa3@aa.com',
#         role=user_role)
#     db.session.add_all(
#     [admin_role, mod_role, user_role, user_john, user_aa, user_susan, user_david])
#
#     db.session.commit()  # 提交至数据库

# db.drop_all()  #删除数据库
# db.create_all() #创建数据库


class db_init_run():
    def __init__(
            self,
            db=db,
            run_mode=run_mode,
            basic_sql_command=basic_sql_command,
            user=user,
            Role=Role,
            db_recover_scheme_detail=db_recover_scheme_detail,
            db_recover_scheme=db_recover_scheme,
            scheme_attr=scheme_attr):
        self.db = db
        self.run_mode = run_mode
        self.basic_sql_command = basic_sql_command
        self.user = user
        self.Role = Role
        self.scheme_attr = scheme_attr
        self.db_recover_scheme = db_recover_scheme
        self.db_recover_scheme_detail = db_recover_scheme_detail



    @staticmethod
    def flush_table(db):
        db.drop_all()
        db.create_all()

    def init_data(self):
            # 初始化加载数据
        self.flush_table(self.db)
        # if not self.db_recover_scheme.query.filter_by(desc='test').first():
        if not self.db_recover_scheme.query.with_entities(db_recover_scheme.id).all(): ##[(1,), (2,), (3,), (4,), (5,)]
            self.db_init_user_role()
            self.db_init_run_mode()
            self.db_init_scheme()
            self.db_init_scheme_attr()
            self.db_init_basic_command_sql()
            self.db_init_scheme_detail()

    def db_init_user_role(self):  # 这里也可以不加 形参数, 上面已加载 user,Role,db
        admin_role = self.Role(name='Admin')
        mod_role = self.Role(name='Moderator')
        user_role = self.Role(name='User')
        user_john = self.user(
            username='john',
            password='123',
            email='aa@aa.com',
            role=admin_role)
        user_aa = self.user(
            username='aa',
            password='123',
            email='aa1@aa.com',
            role=admin_role)
        user_susan = self.user(
            username='susan',
            password='123',
            email='aa2@aa.com',
            role=user_role)
        user_david = self.user(
            username='david',
            password='123',
            email='aa3@aa.com',
            role=user_role)

        self.db.session.add_all(
            [admin_role, mod_role, user_role, user_john, user_aa, user_susan, user_david])

        self.db.session.commit()  # 提交至数据库

    def db_init_run_mode(self):

        l = []
        for i in ['run_sql', 'sql', 'command']:
            run_mode_instance = self.run_mode()
            run_mode_instance.mode = i
            l.append(run_mode_instance)
        self.db.session.add_all(l)
        # print l
        self.db.session.commit()

    def db_init_scheme(self):

        test_scheme = self.db_recover_scheme(desc='test')
        master_scheme = self.db_recover_scheme(desc='master')
        online1_scheme = self.db_recover_scheme(desc='online1')

        self.db.session.add_all(
            [test_scheme, master_scheme, online1_scheme])

        self.db.session.commit()  # 提交至数据库

    def db_init_scheme_attr(self):

        test_scheme = self.db_recover_scheme.query.filter_by(
            desc='test').first()
        master_scheme = self.db_recover_scheme.query.filter_by(
            desc='master').first()
        online1_scheme = self.db_recover_scheme.query.filter_by(
            desc='online1').first()

        lattr = []

        lattr.append({'ip': '10.70.33.107', 'sid': 'cms1'})
        lattr.append({'ip': '10.70.33.108', 'sid': 'cms1'})

        attr_l = []
        for i in lattr:  # 对test_scheme
            attr = self.scheme_attr()
            attr.db_sid = i['sid']
            attr.run_ip = i['ip']
            attr.scheme_id = test_scheme.id        # test scheme方案
            attr_l.append(attr)

        self.db.session.add_all(attr_l)

        self.db.session.commit()  # 提交至数据库

    def db_init_basic_command_sql(self):

        lsql = []
        lsql.append("select STATUS from v$instance")
        lsql.append("select status from v$managed_standby where PROCESS='MRP0'")
        lsql.append("select database_role from v$database")
        lsql.append("alter system switch logfile")
        # print sql_mode.id
        l = []
        for i in lsql:
            bi = self.basic_sql_command()
            bi.cmd_sql = i
            l.append(bi)
        self.db.session.add_all(l)
        # print l
        self.db.session.commit()

    def db_init_scheme_detail(self):

        # scheme 提取
        test_scheme = self.db_recover_scheme.query.filter_by(
            desc='test').first()
        master_scheme = self.db_recover_scheme.query.filter_by(
            desc='master').first()
        online1_scheme = self.db_recover_scheme.query.filter_by(
            desc='online1').first()

        # run_mode 提取
        run_sql_mode = self.run_mode.query.filter_by(mode='run_sql').first()
        sql_mode = self.run_mode.query.filter_by(mode='sql').first()
        command_mode = self.run_mode.query.filter_by(mode='command').first()

        # sql 提取
        select_instance = self.basic_sql_command.query.filter_by(
            cmd_sql="select STATUS from v$instance").first()
        managed_standby = self.basic_sql_command.query.filter_by(
            cmd_sql="select status from v$managed_standby where PROCESS='MRP0'").first()
        alter_logfile = self.basic_sql_command.query.filter_by(
            cmd_sql="alter system switch logfile").first()
        select_database = self.basic_sql_command.query.filter_by(
            cmd_sql="select database_role from v$database").first()

        # ip 提取
        test_ip1 = self.scheme_attr.query.filter_by(
            run_ip='10.70.33.107').first()
        test_ip2 = self.scheme_attr.query.filter_by(
            run_ip='10.70.33.108').first()

        l_detail = []

        l_detail.append({'step': 1, 'verify_str': 'OPEN'})
        l_detail.append({'step': 2, 'verify_str': 'OPEN'})
        l_detail.append({'step': 3, 'verify_str': 'PRIMARY'})
        l_detail.append({'step': 4.111, 'verify_str': 'PHYSICAL STANDBY'})
        l_detail.append({'step': 5, 'verify_str': ''})


        _l = []
        for i in l_detail:  # 对test_scheme
            detail = self.db_recover_scheme_detail()

            detail.step = i['step']
            detail.verify_str = i['verify_str']
            detail.scheme_id = test_scheme.id  # test scheme方向,此处必须用id列,因为models.py中已设置

            if i['step'] == 1:
                detail.basic_sql_command_id = select_instance.id #此处必须用id列
                detail.run_mode_id = sql_mode.id
                detail.attr_id = test_ip1.id

            if i['step'] == 2:
                detail.basic_sql_command_id = select_instance.id
                detail.run_mode_id = sql_mode.id
                detail.attr_id = test_ip2.id

            if i['step'] == 3:
                detail.basic_sql_command_id = select_database.id
                detail.run_mode_id = sql_mode.id
                detail.attr_id = test_ip1.id
            if i['step'] == 4.111:
                detail.basic_sql_command_id = select_database.id
                detail.run_mode_id = sql_mode.id
                detail.attr_id = test_ip2.id
            if i['step'] == 5:
                detail.basic_sql_command_id = alter_logfile.id
                detail.run_mode_id = sql_mode.id
                detail.attr_id = test_ip1.id

            _l.append(detail)

        self.db.session.add_all(_l)

        self.db.session.commit()  # 提交至数据库

    def test_sqldetail(self):
        scheme = db_recover_scheme.query.filter_by(
            desc='test').first()  # 从一 获取所有 多
# >>> scheme.scheme_detail
# <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x0000000003F38F98>
#         scheme.scheme_detail.all()
# [<scheme_detail: 1>, <scheme_detail: 2>, <scheme_detail: 3>, <scheme_detail: 4>]
        dict_scheme_sql = {}
        for d in scheme.scheme_detail.all():
            _d = {}
            _d.setdefault('sql', d.basic_sql_command.cmd_sql)  # 从多获取到一,backref
            _d.setdefault('run_mode', d.run_mode.mode)
            _d.setdefault('verify_str', d.verify_str)
            _d.setdefault('ip',d.scheme_attr.run_ip)
            if _d['run_mode'].find('sql') != -1:
                    _d.setdefault('ip',d.scheme_attr.db_sid)
            dict_scheme_sql.setdefault(d.step, _d)

        print dict_scheme_sql


'''
python manage.py shell   or python manage.py db init ;python manage.py db migrate ;python manage.py db upgrade 
>>> dd=db_init_run()
>>> dd.flush_table(db)  ##shell 命令，重新创建表，跳过此步
>>> dd.init_data()
>>> scheme = db_recover_scheme.query.filter_by(
...             desc='test').first()
>>> scheme.scheme_detail
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x0000000004242470>
>>> scheme.scheme_detail.all()
[<scheme_detail: 1>, <scheme_detail: 2>, <scheme_detail: 3>, <scheme_detail: 4>, <scheme_detail: 5>]
>>> dd.test_sqldetail()
{1: {'ip': u'10.70.33.107', 'verify_str': u'OPEN', 'run_mode': u'sql', 'sql': u'select STATUS from v$instance'}, 2: {'ip': u'10.70.33.108', 'verify_str': u'OPEN', 'run_mode': u'sql', 'sql': u'select STATUS from v$
instance'}, 3: {'ip': u'10.70.33.107', 'verify_str': u'PRIMARY', 'run_mode': u'sql', 'sql': u'select database_role from v$database'}, 4: {'ip': u'10.70.33.108', 'verify_str': u'PHYSICAL STANDBY', 'run_mode': u'sql
', 'sql': u'select database_role from v$database'}, 5: {'ip': u'10.70.33.107', 'verify_str': u'', 'run_mode': u'sql', 'sql': u'alter system switch logfile'}}


'''
